package dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;

import modelo.RelFuncionarios;

public class DaoRelFuncionarios {

    public ArrayList<RelFuncionarios> consultarRelFuncionarios(Date dataini, Date datafin) {
    	
    	ArrayList<RelFuncionarios> servicos = new ArrayList<RelFuncionarios>();
    	
      try {
        Class.forName("org.postgresql.Driver");
            Connection con = null;
             con = DriverManager.getConnection("jdbc:postgresql:Siafu","postgres", "postgresql123");
       if (con != null)
          {
                
         
    	   		  Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);

                  String query="select nome_func, nome_plano , count(nome_plano) from servico, funcionario, beneficiario, venda, plano " +
                		  		"where dt_servico >= '" + dataini + "' and " +
                		        "dt_servico <= '" + datafin + "' and " + 
                		  		"funcionario.id_func = servico.id_func and " +
                		  		"servico.id_benef = beneficiario.id_benef and " +
                		  		"beneficiario.id_venda = venda.id_venda and " +
                		  		"venda.id_plano = plano.id_plano " +
                		  		"group by nome_func, nome_plano " + 
                		  		"order by nome_func; ";
                  ResultSet rs = stmt.executeQuery(query);
                  
                  while(rs.next()){
                	  RelFuncionarios rel = new RelFuncionarios();
                	  
                	    int cols = rs.getMetaData().getColumnCount();
                	    Object[] arr = new Object[cols];
                	    for(int i=0; i<cols; i++){
                	      arr[i] = rs.getObject(i+1);
                	      rel.setNome_func(rs.getString(1));                	      
                	      rel.setNome_plano(rs.getString(2));
                	      rel.setQtd_planos(rs.getInt(3));
                	    }
                	    servicos.add(rel);
                	}
                  
          }
          else
              System.out.println("Connection Failed!");
      }
          catch(Exception e){
                              e.printStackTrace();
          }
      
    
      
      return servicos;
    
    }
    
    
}
